package com.hw.dagl.service.impl;

import com.hw.dagl.model.Driver_audit;
import com.hw.dagl.model.Sys_Role;
import com.hw.dagl.model.Vehicle;
import com.hw.dagl.service.YxxxglService;
import com.hw.dao.utils.*;
import com.hw.interceptor.LogAspectBean;
import com.hw.interceptor.SystemLog;
import com.hw.utils.StringUtils;

import freemarker.cache.URLTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.MalformedTemplateNameException;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.TemplateNotFoundException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

@Service
public class YxxxglServiceImpl extends LogAspectBean implements YxxxglService{

	
    private Logger logger=LoggerFactory.getLogger(YxxxglServiceImpl.class);
    
    @Resource
  	private OraclePageDaoBase oraclePageDaoBase;
  
    @Resource
  	private OraclePageDaoBase1 oraclePageDaoBase1;
  
  /*  private Vehicle oldBean;
	private Vehicle bean;

	public Vehicle getOldBean() {
		return oldBean;
	}

	public void setOldBean(Vehicle oldBean) {
		this.oldBean = oldBean;
	}

	public Vehicle getBean() {
		return bean;
	}

	public void setBean(Vehicle bean) {
		this.bean = bean;
	}*/
  	/**
  	 * 影像查询列表
  	 */
	@Override
	public Page<Vehicle> findYxsqxzxx(Page<Vehicle> pages, Vehicle info,
			String startTime, String endTime,Sys_Role sysRole) {
		List<Object> argsveh = new ArrayList<Object>();
		StringBuffer sqlveh= new StringBuffer("");
		 String hphm=info.getHphm();
	        if(StringUtils.isNotEmpty(hphm)){
	        	sqlveh.append(" select distinct xh from vehicle_record where 1=1 and hphm = ? ");
	        	argsveh.add(hphm);
	        }
	        if(StringUtils.isNotEmpty(info.getHpzl())){
	        	sqlveh.append(" AND hpzl = ? ");
	        	argsveh.add(info.getHpzl());
	        }
	        List<Vehicle> list = null;
	        if(StringUtils.isNotEmpty(hphm)){
	        if(sysRole.getRid().equals("43")){
				list =  oraclePageDaoBase1.getRemoteJdbcTemplate().query(sqlveh.toString(),argsveh.toArray(),new RowMapper<Vehicle>() {
					private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
					@Override
					public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
						Vehicle veh = new Vehicle();
						et.setColumnsValueByResultSet(arg0, veh);
						return veh;
					}
				});
			}else{
				list =  oraclePageDaoBase.query(sqlveh.toString(), argsveh.toArray(),new RowMapper<Vehicle>() {
					private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
					@Override
					public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
						Vehicle veh = new Vehicle();
						et.setColumnsValueByResultSet(arg0, veh);
						return veh;
					}
				});
			}
	        if(list==null || list.size()<1) return new Page<Vehicle>();
	     }
	      
		StringBuffer sql =new StringBuffer("select t.*,'皖'||t.hphm hphmmc,case when t.sjly='0' then h.xm else t.cjr end  cjrxm "
		        + " ,(SELECT applyNo from dagl_T_Audit where 1=1 and applicant=? and trim(lsh)=t.lsh and trim(xh)=t.xh and zt='1' AND permissiontype='03' and flag='1') AS scqx"
		        + " from VEHICLE_RECORD t\n" +
						//"left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl\n" + 
						//"left join VEH_OPERTYPE r on t.ywlx=r.ywlx and t.ywyy=r.ywyy \n" + 
						"left join SYS_USER h on t.cjr=h.yhdh where 1=1 ");
		    List<Object> args = new ArrayList<Object>();
		    String cjr=info.getCjr();//此处创建人就是当前登录人
		    args.add(cjr);
		   
			if(StringUtils.isNotEmpty(hphm)){
			String xhStrs="";
	        if(list.size()>0){
	        	for(int i=0; i<list.size();i++){
	        		if(i==0) xhStrs+="'"+list.get(i).getXh()+"'";
	        		else xhStrs+=",'"+list.get(i).getXh()+"'";
	        	}
	          sql.append(" AND t.xh in ("+xhStrs+") ");
	         // args.add(xhStrs);
	        	
	        } 
			}
	        if(StringUtils.isNotEmpty(info.getHpzl())){
	        	sql.append(" AND t.hpzl = ? ");
	        	args.add(info.getHpzl());
	        }
	        String dabh=info.getDabh();
	        if(StringUtils.isNotEmpty(dabh)){
				sql.append(" AND t.dabh = ? ");
				args.add(dabh);
			}
	        if(StringUtils.isNotEmpty(info.getLsh())) {
				sql.append(" and t.lsh =? ");
				args.add(info.getLsh());
			}
	        
	        sql.append(" order by t.cjsj desc ");
	        
	        
	    Page<Vehicle> pages2;
		if(sysRole.getRid().equals("43")){
			pages2 =  oraclePageDaoBase1.queryForPage(sql.toString(), pages,new RowMapper<Vehicle>() {
				private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
				@Override
				public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
					Vehicle veh = new Vehicle();
					et.setColumnsValueByResultSet(arg0, veh);
					return veh;
				}
			}, args.toArray());
		}else{
			pages2 =  oraclePageDaoBase.queryForPage(sql.toString(), pages,new RowMapper<Vehicle>() {
				private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
				@Override
				public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
					Vehicle veh = new Vehicle();
					et.setColumnsValueByResultSet(arg0, veh);
					return veh;
				}
			}, args.toArray());
		}
		return pages2;
	}
	@Override
	@SystemLog(Modelname="机动车档案详情",operationType="3",operationName="查看档案详情")
	public Vehicle findYxxxglByxh(Vehicle veh, String startTime, String endTime,Sys_Role sysRole) {		
	    //根据角色切换数据源
	    //oraclePageDaoBase.setSourceByRole(sysRole);
		
		
	    /*StringBuffer sql=new StringBuffer("select g.*,hp.dmsm1 hpzlmc,'皖'||g.hphm hphmmc,r.ywmc from\n"+
				"(select t.*,r.tpurl from VEHICLE_RECORD t,VEHICLE_PIC r where t.lsh=r.lsh and t.xh=r.xh");
	    if (StringUtils.isNotEmpty(veh.getLsh())) {
	    	sql.append(" AND t.lsh =  '" + veh.getLsh()+ "'");
	    }	
	    sql.append(
		" )  g "
		+ "left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=g.hpzl\n"
		+ "left join VEH_OPERTYPE r on g.ywlx=r.ywlx and g.ywyy=r.ywyy\n" );*/
		
		StringBuffer sql=new StringBuffer("select g.*,hp.dmsm1 hpzlmc,'皖'||g.hphm hphmmc from\n"+
				"(select t.*,r.tpurl from VEHICLE_RECORD t,VEHICLE_PIC r where t.lsh=r.lsh and t.xh=r.xh");
	    if (StringUtils.isNotEmpty(veh.getLsh())) {
	    	sql.append(" AND t.lsh =  '" + veh.getLsh()+ "'");
	    }
	    sql.append(
		" )  g "
		+ "left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=g.hpzl\n" );
	    

	    //queryForObject在查询不到数据的时候会报空指针异常
	    List<Vehicle> vehicles;
	    if(sysRole.getRid().equals("43")){
	    	vehicles = oraclePageDaoBase1.getRemoteJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<Vehicle>(Vehicle.class));
		}else{
			vehicles = oraclePageDaoBase.query(sql.toString(),new BeanPropertyRowMapper<Vehicle>(Vehicle.class));	
		}
	    
		if(vehicles.size()>0){
			 veh=vehicles.get(0);
		    	StringBuffer buf=new StringBuffer();
		    	char[] ch;
		    	if(veh.getYwyy()!=null && veh.getYwyy().length()>=1){
		    		 List<Vehicle> operList=queryOpertype(veh.getYwlx());
		    		 ch=veh.getYwyy().toCharArray();
		    		 for(int i = 0;i<ch.length;i++){
		    			 for(Vehicle v : operList){
		    				 if(v.getYwyy().equals(String.valueOf(ch[i]))){
		    					 if(i==0){
		    						 buf.append(v.getYwmc());
		    					 }else{
		    						 buf.append("|"+v.getYwmc());	 
		    					 }
		    				 }
		    			 }
		    			}
		    		 veh.setYwmc(buf.toString());
		    	}
		}else{
			veh=new Vehicle();
		}
		 return veh;
	}
	public List<Vehicle> queryOpertype(String ywlx) {
		String sqlOpertype ="select t.ywlx,ywyy,ywmc from VEH_OPERTYPE t where  ywlx=?";
	    List<Vehicle> listOper=oraclePageDaoBase.query(sqlOpertype.toString(),new Object[]{ywlx}, new BeanPropertyRowMapper<Vehicle>(Vehicle.class));
		return listOper;
	}
	@Override
	@SystemLog(Modelname="影像申请下载详情",operationType="3",operationName="查询详情")
	public Vehicle findYxsqxzByxhFirstIn(Vehicle info, String startTime, String endTime) {
		String sql="select t.*,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE_RECORD t where 1=1 ";
	    if (StringUtils.isNotEmpty(startTime)) {
	      sql = sql + " AND t.cjsj >= to_date('" + startTime + "','yyyy-MM-dd hh24:mi:ss')";
	    }

	    if (StringUtils.isNotEmpty(info.getHpzl())) {
            sql = sql + " AND t.hpzl =  '" + info.getHpzl() + "'";
        }
	    
	    if (StringUtils.isNotEmpty(endTime)) {
	      sql = sql + " AND t.cjsj <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')";
	    }
	 
	    String xh=info.getXh();
	    if (StringUtils.isNotEmpty(xh)) {
	      sql = sql + " AND xh =  '" + xh+ "'";
	    }		    
		return oraclePageDaoBase.queryForObject(sql.toString(),new BeanPropertyRowMapper<Vehicle>(Vehicle.class));
	}

	@Override
	public GcPage<Vehicle> findYxsqxzByxhUpAndDown(GcPage<Vehicle> pages,
			Vehicle info, String startTime, String endTime) {
		String sql="select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t where 1=1 and t.yxzt=2";
		//List<Object> args = new ArrayList<Object>();
			if (StringUtils.isNotEmpty(info.getHphm())) {
			    sql = sql + " and t.hphm = '" + info.getHphm() + "'";
			}
			/*String hphm="";
			try {
				hphm = URLDecoder.decode(info.getHphm(), "UTF-8");
				info.setHphm(hphm);
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}			
		      sql = sql + " AND t.hphm = '" + info.getHphm() + "'";
		    }*/

		    if (StringUtils.isNotEmpty(info.getHpzl())) {
		      sql = sql + " AND t.hpzl =  '" + info.getHpzl() + "'";
		    }
		    if (StringUtils.isNotEmpty(startTime)) {
		      sql = sql + " AND t.ccdjrq >= to_date('" + startTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }

		    if (StringUtils.isNotEmpty(endTime)) {
		      sql = sql + " AND t.ccdjrq <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }	    
		   // sql+=" order by ccdjrq desc ";		    	    
		return oraclePageDaoBase.queryForGcPage(sql.toString(), pages, new RowMapper<Vehicle>() {
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}
		}, new Object[]{});
	}

	@Override
	public List<Vehicle> findYxshByxh(Vehicle info) {
		StringBuffer sql =new StringBuffer("select * from VEHICLE t,VEHICLE_PIC r where t.xh=r.xh");
		if(StringUtils.isNumeric(info.getXh())){
			sql.append(" and xh= '"+info.getXh()+"'");
		}		
		return oraclePageDaoBase.query(sql.toString(), new RowMapper<Vehicle>(){
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}});
	}

	@Override
	@SystemLog(Modelname="影像下载审核",operationType="2",operationName="单个修改下载审核状态")
	@Transactional(readOnly=false,rollbackFor=Exception.class)
	public int updateYxxzzt(Vehicle info) {
		String sql="update Vehicle set yxxzzt=? where xh=?";
		List<Object> args = new ArrayList<Object>();
		if (StringUtils.isNotEmpty(info.getYxxzzt())&&StringUtils.isNotEmpty(info.getXh())) {
			args.add(info.getYxxzzt());
			args.add(info.getXh());
		 }else{
			 return 0;
		 }
		int num= oraclePageDaoBase.update(sql, args.toArray());
		return num;
	}

	@Override
	@SystemLog(Modelname="影像下载审核",operationType="2",operationName="批量修改下载审核状态")
	@Transactional(readOnly=false,rollbackFor=Exception.class)
	public int updateYxsqxzBatch(final Vehicle info) {
		String sql="update Vehicle set yxxzzt=? where xh in(?)";
		final String[] str=info.getXh().split(",");
		int[] num= oraclePageDaoBase.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, info.getYxxzzt());
				ps.setString(2, str[i]);
			}
			
			@Override
			public int getBatchSize() {
				return str.length;
			}
		});
		return num.length;
	}

	
	/**
  	 * 分页查询影像下载审核列表
  	 */
	@Override
	@SystemLog(Modelname="影像下载审核",operationType="3",operationName="查询列表")
	public Page<Vehicle> findYxshxzxx(Page<Vehicle> pages, Vehicle info,
			String startTime, String endTime) {
		StringBuffer sql =new StringBuffer("select t.*,csys.dmsm1 csysmc,hp.dmsm1 hpzlmc,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc "
				+ ",substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t  "
				+ " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl "//机动车号牌种类代码
        		+ " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1008' and t.xtlb='00') csys on csys.dmz=t.csys where t.yxzt=2 and t.yxxzzt=1 ");//机动车车身颜色基本色调代码
		 List<Object> args = new ArrayList<Object>();
		 SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");	
			if(StringUtils.isNotEmpty(startTime)) {
				sql.append(" and t.ccdjrq >= ? ");
				try {
					args.add(formatDate.parse(startTime));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(StringUtils.isNotEmpty(endTime)) {
				sql.append(" AND t.ccdjrq <= ? ");
				try {
					args.add(formatDate.parse(endTime));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
	        if(StringUtils.isNotEmpty(info.getHphm())){
	            sql.append(" AND t.hphm = ? ");
	            args.add(info.getHphm());
	        }
	        if(StringUtils.isNotEmpty(info.getHpzl())){
	            sql.append(" AND t.hpzl = ? ");
	            args.add(info.getHpzl());
	        }
	        sql.append(" order by ccdjrq desc ");
		return oraclePageDaoBase.queryForPage(sql.toString(), pages,new RowMapper<Vehicle>() {
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}
		}, args.toArray());
	}

	@Override
	@SystemLog(Modelname="影像下载审核详情",operationType="3",operationName="查询详情")
	public GcPage<Vehicle> findYxshxzByxhFirstIn(GcPage<Vehicle> pages,
			Vehicle info, String startTime, String endTime) {
		/*String sql="select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t,VEHICLE_pic g  "
				  + " where t.xh=g.xh(+) ";
		String sql2="select * from (select v.*,rownum rowNumber  "
				+ "from (select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t,VEHICLE_pic g " 
                + " where t.xh=g.xh(+) ";*/
		String sql="select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t where t.yxzt=2 and t.yxxzzt=1 ";
		String sql2="select * from (select v.*,rownum rowNumber  "
				+ "from (select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE t where t.yxzt=2 and t.yxxzzt=1 ";
		List<Object> args = new ArrayList<Object>();
			if (StringUtils.isNotEmpty(info.getHphm())) {
			    sql = sql + " and t.hphm = '" + info.getHphm() + "'";
			    sql2 = sql2 + " and t.hphm = '" + info.getHphm() + "'";
			}
			/*String hphm="";
			try {
				hphm = URLDecoder.decode(info.getHphm(), "UTF-8");
				info.setHphm(hphm);
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}			
		      sql = sql + " AND t.hphm = '" + info.getHphm() + "'";
		    }*/

		    if (StringUtils.isNotEmpty(info.getHpzl())) {
		      sql = sql + " AND t.hpzl =  '" + info.getHpzl() + "'";
		      sql2 = sql2 + " AND t.hpzl =  '" + info.getHpzl() + "'";
		    }
		   // SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		    if (StringUtils.isNotEmpty(startTime)) {
		      sql = sql + " AND t.ccdjrq >= to_date('" + startTime + "','yyyy-MM-dd hh24:mi:ss')";
		      sql2 = sql2 + " AND t.ccdjrq >= to_date('" + startTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }

		    if (StringUtils.isNotEmpty(endTime)) {
		      sql = sql + " AND t.ccdjrq <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')";
		      sql2 = sql2 + " AND t.ccdjrq <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }
		    sql2 = sql2 + " order by t.ccdjrq desc ) v ) where 1=1 ";
		    if (StringUtils.isNotEmpty(info.getXh())) {
		      sql2 = sql2 + " AND xh =  '" + info.getXh() + "'";
		      args.add(info.getXh());
		    }		    
		    sql+=" order by ccdjrq desc ";
		    List<Vehicle> list=oraclePageDaoBase.query(sql2, new RowMapper<Vehicle>() {
				private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
				@Override
				public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
					Vehicle veh = new Vehicle();
					et.setColumnsValueByResultSet(arg0, veh);	
					return veh;
				}
		    	   
			});
		    pages.setPage(list.get(0).getRowNumber());	    
		return oraclePageDaoBase.queryForGcPage(sql.toString(), pages, new RowMapper<Vehicle>() {
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}
		}, new Object[]{});
	}

	@Override
	public GcPage<Vehicle> findYxshxzByxhUpAndDown(GcPage<Vehicle> pages,
			Vehicle info, String startTime, String endTime) {
		String sql="select t.*,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc,substr(t.fzjg,0,1)||t.hphm hphmmc "
				+ " from VEHICLE t where 1=1 and t.yxzt=2 and t.yxxzzt=1 ";
			if (StringUtils.isNotEmpty(info.getHphm())) {
			    sql = sql + " and t.hphm = '" + info.getHphm() + "'";
			}
			/*String hphm="";
			try {
				hphm = URLDecoder.decode(info.getHphm(), "UTF-8");
				info.setHphm(hphm);
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}			
		      sql = sql + " AND t.hphm = '" + info.getHphm() + "'";
		    }*/

		    if (StringUtils.isNotEmpty(info.getHpzl())) {
		      sql = sql + " AND t.hpzl =  '" + info.getHpzl() + "'";
		    }
		    if (StringUtils.isNotEmpty(startTime)) {
		      sql = sql + " AND t.ccdjrq >= to_date('" + startTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }

		    if (StringUtils.isNotEmpty(endTime)) {
		      sql = sql + " AND t.ccdjrq <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')";
		    }	    
		   // sql+=" order by ccdjrq desc ";		    	    
		return oraclePageDaoBase.queryForGcPage(sql.toString(), pages, new RowMapper<Vehicle>() {
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}
		}, new Object[]{});
	}

	@Override
	@SystemLog(Modelname="影像下载申请",operationType="2",operationName="批量修改下载申请状态")
	@Transactional(readOnly=false,rollbackFor=Exception.class)
	public int updateYxxzztBatch(final Vehicle info) {
		//String sql="update Vehicle set yxxzzt="+info.getYxxzzt()+" where xh in('"+info.getXh().replaceAll(",", "','")+"')"; //('"+ids+"')
		String sql="update Vehicle set yxxzzt=? where xh in(?)"; //('"+ids+"')
		/*if (StringUtils.isNotEmpty(info.getYxxzzt())&&StringUtils.isNotEmpty(info.getXh())) {
			args.add(info.getYxxzzt());
			args.add("'"+info.getXh().replaceAll(",", "','")+"'");
		 }else{
			 return 0;
		 }*/
		final String [] str =info.getXh().split(",");
		int[] num= oraclePageDaoBase.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, info.getYxxzzt());
				ps.setString(2, str[i]);			
			}			
			@Override
			public int getBatchSize() {
				return str.length;
			}
		});
		return num.length;
	}

	@Override
	public List<Vehicle> findYxxxByIds(String ids) {
		ids = ids.replaceAll(",","','");
		String sql="select * from VEHICLE where xh in ('"+ids+"')";
		return oraclePageDaoBase.query(sql, new RowMapper<Vehicle>(){
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}});
	}

	/*@Override
	public List<Vehicle> findYxxxPicByIds(String ids) {
		ids = ids.replaceAll(",","','");
		String sql="select * from VEHICLE_PIC where xh in ('"+ids+"')";
		return oraclePageDaoBase.query(sql, new RowMapper<Vehicle>(){
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}});
	}*/
	@Override
	@SystemLog(Modelname="档案详情下载",operationType="5",operationName="下载档案")
	public Vehicle findYxxxPicByxh(String lsh,String xh) {
		String sql="select * from VEHICLE_PIC where lsh ='"+lsh+"'"+ " and xh='"+xh+"'";
		List<Vehicle> list =oraclePageDaoBase.query(sql, new RowMapper<Vehicle>(){
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}});
		return list.size()>0 ? list.get(0) :null;
	}

	@Override
	public List<Vehicle> findyxxxsqztByIds(String ids) {
		ids = ids.replaceAll(",","','");
		String sql="select * from VEHICLE where xh in ('"+ids+"')";
		return oraclePageDaoBase.query(sql, new RowMapper<Vehicle>(){
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Vehicle veh = new Vehicle();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}});
	}


	@Override
	public Page<Driver_audit> findJszByPage(Page<Driver_audit> pages, Driver_audit info, String startTime, String endTime) {
		StringBuilder sql=new StringBuilder("select xh,dabh,sfzmhm,xm,decode(xb,0,'女','1','男') AS xb,fzjg,cjsj,to_char(cjsj,'yyyy-MM-dd hh24:mi:ss') as strCjsj,gxsj,cjr from driver_audit where 1=1 ");
		List<String> paramList=new ArrayList<String>();
		if(StringUtils.isNotEmpty(startTime)){
			sql.append(" and cjsj>=to_date(?,'yyyy-MM-dd hh24:mi:ss') ");
			paramList.add(startTime);
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql.append(" and cjsj<=to_date(?,'yyyy-MM-dd hh24:mi:ss') ");
			paramList.add(endTime);
		}
		String sfzmhm=info.getSfzmhm();
		if(StringUtils.isNotEmpty(sfzmhm)){
			sql.append(" and instr(sfzmhm,?)>0 ");
			paramList.add(sfzmhm);
		}
		return oraclePageDaoBase.queryForPage(sql.toString(), pages, new RowMapper<Driver_audit>() {
			private EntityTools<Driver_audit> et = new EntityTools<Driver_audit>();
			@Override
			public Driver_audit mapRow(ResultSet arg0, int arg1)
					throws SQLException {
				Driver_audit veh = new Driver_audit();
				et.setColumnsValueByResultSet(arg0, veh);
				return veh;
			}
		},paramList.toArray());
	}

	@Override
	public Driver_audit findJszByxh(String xh) {
		String sql="select xm,decode(xb,0,'女',1,'男') as xb,fzjg,sfzmhm,xh from driver_audit where xh='"+xh+"' ";
		List<Driver_audit> Driver_audits=oraclePageDaoBase.query(sql,new BeanPropertyRowMapper<Driver_audit>(Driver_audit.class));
		int size=Driver_audits.size();
		if(size>0){
			return Driver_audits.get(0);
		}else{
			return new Driver_audit();
		}
	}

	@Override
    public List<Vehicle> queryVehicleList(Vehicle info, String startTime, String endTime, PageUtil page) {
        long currentPage=page.getCurrentPage();//当前页数
        long pageSize=page.getPageSize();//每页大小
        long max=(currentPage+1)*pageSize;
        long min=(currentPage-1)*pageSize;
        StringBuilder sql=new StringBuilder("select * from (");
        sql.append(" select ROWNUM rn,xh FROM ( ");
        sql.append(" select xh FROM VEHICLE_AUDIT t where 1=1 ");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" AND t.cjsj >= to_date('"+ startTime+"','yyyy-MM-dd hh24:mi:ss')");
        }

        if (StringUtils.isNotEmpty(endTime)) {
           sql.append(" AND t.cjsj <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')");
        }
       
        String hpzl=info.getHpzl();
        if (StringUtils.isNotEmpty(hpzl)) {
           sql.append(sql + " AND t.hpzl='"+ hpzl+ "'");
        }
        sql.append(" ORDER BY T.CJSJ DESC ");
        sql.append(" ) where rownum<'"+max+"' ");
        sql.append(") where rn>'"+min+"' ");
        logger.info("详情页面--查询的sql  "+sql.toString());
        List<Vehicle> vehicles= oraclePageDaoBase.query(sql.toString(), new BeanPropertyRowMapper<Vehicle>(Vehicle.class));
        return vehicles;
    }
	@Override
	public  List<Vehicle>  getvehicleList(String startTime, String endTime, String hphm, String hpzl) {
	        StringBuffer sql = new StringBuffer("select t.dabh,'皖'||t.hphm hphmmc from VEHICLE_RECORD t where 1=1 ");
	        if (StringUtils.isNotEmpty(startTime)) {
	            sql.append(" AND t.cjsj >= to_date('"+ startTime+"','yyyy-MM-dd hh24:mi:ss')");
	        }

	        if (StringUtils.isNotEmpty(endTime)) {
	           sql.append(" AND t.cjsj <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')");
	        }
	        if (StringUtils.isNotEmpty(hphm)) {
		           sql.append(" AND t.hphm='"+ hphm+ "'");
		        }
	        if (StringUtils.isNotEmpty(hpzl)) {
	           sql.append(" AND t.hpzl='"+ hpzl+ "'");
	        }
	        List<Vehicle> vehicles= oraclePageDaoBase.query(sql.toString(), new BeanPropertyRowMapper<Vehicle>(Vehicle.class));
	        
			return vehicles;  
	          
	         
	    }
	
	@Override
	public int countVehice(String startTime, String endTime, String hphm, String hpzl) {
		StringBuffer sql = new StringBuffer("select count(t.hphm) from VEHICLE_RECORD t where 1=1 ");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" AND t.cjsj >= to_date('"+ startTime+"','yyyy-MM-dd hh24:mi:ss')");
        }

        if (StringUtils.isNotEmpty(endTime)) {
           sql.append(" AND t.cjsj <= to_date('" + endTime + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (StringUtils.isNotEmpty(hphm)) {
	           sql.append(" AND t.hphm='"+ hphm+ "'");
	        }
        if (StringUtils.isNotEmpty(hpzl)) {
           sql.append(" AND t.hpzl='"+ hpzl+ "'");
        }
        int i = oraclePageDaoBase.queryForInt(sql.toString());
        return i;
	}
	@Override
	public Page<Vehicle> findvehicledayjPage(Page<Vehicle> pages, Vehicle info, String startTime, String endTime) {
				StringBuffer sql =new StringBuffer(
						"select t.*,hp.dmsm1 hpzlmc,'皖'||t.hphm hphmmc,case when t.sjly='0' then h.xm else t.cjr end  cjrxm,r.ywmc from VEHICLE_RECORD t\n" +
						"left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl\n" + 
						"left join VEH_OPERTYPE r on t.ywlx=r.ywlx and t.ywyy=r.ywyy\n" + 
						"left join SYS_USER h on t.cjr=h.yhdh where 1=1 ");
		 List<Object> args = new ArrayList<Object>();
		     sql.append(" and t.cjr=? ");
		     args.add(info.getCjr());
		 if(StringUtils.isNotEmpty(startTime)) {
				sql.append(" and t.cjsj >= to_date(?,'yyyy-MM-dd hh24:mi:ss') ");
				args.add(startTime);
			}
			if(StringUtils.isNotEmpty(endTime)) {
				sql.append(" AND t.cjsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
				args.add(endTime);
			}
		    if(StringUtils.isNotEmpty(info.getHphm())){
		        sql.append(" AND t.hphm = ? ");
		        args.add(info.getHphm());
		    }
		    if(StringUtils.isNotEmpty(info.getHpzl())){
		        sql.append(" AND t.hpzl = ? ");
		        args.add(info.getHpzl());
		    }
		    if(StringUtils.isNotEmpty(info.getLsh())){
		        sql.append(" AND t.lsh = ? ");
		        args.add(info.getLsh());
		    }
		    sql.append(" order by t.cjsj desc ");
		 
		    final StringBuffer buf=new StringBuffer();
			//final char[] ch;
		    
		return oraclePageDaoBase.queryForPage(sql.toString(), pages,new RowMapper<Vehicle>() {
			private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
			@Override
			public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
				Vehicle veh = new Vehicle();
				char[] ch;
				et.setColumnsValueByResultSet(arg0, veh);
				if(veh.getYwyy().length()>1){
		    		 List<Vehicle> operList=queryOpertype(veh.getYwlx());
		    		 ch=veh.getYwyy().toCharArray();
		    		 for(int i = 0;i<ch.length;i++){
		    			 for(Vehicle v : operList){
		    				 if(v.getYwyy().equals(String.valueOf(ch[i]))){
		    					 if(i==0){
		    						 buf.append(v.getYwmc());
		    					 }else{
		    						 buf.append("|"+v.getYwmc());
		    					 }
		    				 }
		    			 }
		    			}
		    		 veh.setYwmc(buf.toString());
		    	}
				return veh;
			}
		}, args.toArray());
}  		
   
    
}